package sqlite.demo;

import sqlite.demo.R;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

public class SQLiteActivity extends Activity {

	Button btnCreateDatabase = null;
	Button btnCreateTable = null;
	Button btnInsertRecord = null;
	Button btnUpdateRecord = null;
	Button btnDeleteRecord = null;
	Button btnQueryRecord = null;
	TextView textViewResult = null;
	Button btnDeleteDatabase =null;
	Button btnDeleteTable = null;
	
	SQLiteDatabase SQLiteDb = null;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        textViewResult = (TextView)this.findViewById(R.id.textViewResult);
        btnCreateDatabase = (Button)this.findViewById(R.id.btnCreateDatabase);
        btnCreateTable = (Button)this.findViewById(R.id.btnCreateTable);
        btnInsertRecord = (Button)this.findViewById(R.id.btnInsertRecord);
        btnUpdateRecord = (Button)this.findViewById(R.id.btnUpdateRecord);
        btnDeleteRecord = (Button)this.findViewById(R.id.btnDeleteRecord);
        btnQueryRecord = (Button)this.findViewById(R.id.btnQueryRecord);
        btnDeleteDatabase = (Button)this.findViewById(R.id.btnDeleteDatabase);
        btnDeleteTable = (Button)this.findViewById(R.id.btnDeleteTable);
        
        /**
         * 在Android 中创建和打开一个数据库都可以使用openOrCreateDatabase 方法来实现，因为它会自动去检测是否存在这个数据库，如果存在则打
         * 开，不过不存在则创建一个数据库；创建成功则返回一个 SQLiteDatabase对象，否则抛出异常FileNotFoundException.
         */
        btnCreateDatabase.setOnClickListener(new View.OnClickListener() {
			
			public void onClick(View v) {
		    	SQLiteDb = getDatabaseConn();
			}
		});
        
        btnCreateTable.setOnClickListener(new View.OnClickListener() {
			
     			public void onClick(View v) {
     				if (SQLiteDb == null) {
     		    		SQLiteDb = getDatabaseConn();
     		    	} 
     				//SQLiteDb.execSQL("CREATE TABLE person (_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age SMALLINT)"); 
     		    	SQLiteDb.execSQL("create table user(id int, username varchar(20),  age int)");
     		    	SQLiteDb.close();
     			}
     		});

        /**
         * 3.向表中添加一条数据
         * 可以使用insert 方法来添加数据，但是 insert 方法要求把数据都打包到 ContentValues 中， ContentValues 其实就是一个Map， key值是字段名称， 
         * Value值是字段的值。通过 ContentValues 的 put 方法就可以把数据放到ContentValues中，然后插入到表中去。
         */
        
        btnInsertRecord.setOnClickListener(new View.OnClickListener() {
			
			public void onClick(View v) {
				
				ContentValues values = new ContentValues();
				values.put("id", 1);
		    	values.put("username", "Justin");
		    	values.put("age", 20);
		    	
		    	if (SQLiteDb == null) {
		    		SQLiteDb = getDatabaseConn();
		    	} 
		    	
		    	if (! SQLiteDb.isOpen()) {
		    		//SQLiteDb.openDatabase("test", null, SQLiteDb.OPEN_READWRITE);
		    		SQLiteDb = SQLiteDatabase.openDatabase("test", null, SQLiteDatabase.OPEN_READWRITE);
		    	}
		    	
		    	SQLiteDb.insert("User", null, values);
		    	SQLiteDb.close();
		    	//这样同样可以使用execSQL方法来执行一条“插入”的SQL语句，代码如下：    
		    	//String  INSERT_DATA = "INSERT INTO table1 (_id, username, age) values (1, 'Justin', 20)" ;    
		    	//SQLiteDb.execSQL(INSERT_DATA);
			}
		});
        
        btnUpdateRecord.setOnClickListener(new View.OnClickListener() {
			
			public void onClick(View v) {
				ContentValues values = new ContentValues();
		    	values.put("username", "emma");
		    	
		    	if (SQLiteDb == null) {
		    		SQLiteDb = getDatabaseConn();
		    	} 
		    	
		    	SQLiteDb.update("user", values, "id=?", new String[] { "1" });
		    	SQLiteDb.close();
			}
		});

        btnQueryRecord.setOnClickListener(new View.OnClickListener() {
			
			public void onClick(View v) {
				if (SQLiteDb == null) {
					SQLiteDb = getDatabaseConn();
				}
				
				int id = 0;
				int age = 0;
				String username = "";
				
				Cursor cursor = SQLiteDb.query("user", new String[] {"id", "username", "age"}, "id=?", new String[] { "1" }, null, null, null);
				
				while (cursor.moveToNext()) {
					id = cursor.getInt(cursor.getColumnIndex("id"));
					age = cursor.getInt(cursor.getColumnIndex("age"));
					username = cursor.getString(cursor.getColumnIndex("username"));
				}
				
				textViewResult.setText("id:" + id + "   username:" + username + "   age:" + age);
			}
		});
        
        btnDeleteDatabase.setOnClickListener(new View.OnClickListener() {
			
  			public void onClick(View v) {
  				deleteDatabase("test");  
  			}
  		});
        
        btnDeleteTable.setOnClickListener(new View.OnClickListener() {
			
			public void onClick(View v) {
				if (SQLiteDb == null) {
		    		SQLiteDb = getDatabaseConn();
		    	} 
				
				SQLiteDb.execSQL("drop table if exists user");  
			}
		});

        btnDeleteRecord.setOnClickListener(new View.OnClickListener() {
			
			public void onClick(View v) {
				
			}
		});
    }

    private SQLiteDatabase getDatabaseConn() {
    	return openOrCreateDatabase("test", MODE_PRIVATE, null);
    }
    
}